package DAO.MysqlDAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import DAO.InterfaceDAO.ClasseDAO;
import Factories.FactoryMySqlDAO;
import Model.Classe;
import Model.Etudiant;
import Model.Professeur;
import Model.Resultat;

public class ClasseDAO_Mysql implements ClasseDAO {

	private PreparedStatement psCreate, psRead, psUpdate, psDelete, psFind, psEleves, psExist;

	public ClasseDAO_Mysql(Connection cx) {		
		try {
			psCreate = cx.prepareStatement("INSERT INTO classe VALUES( ?, ?)");
			psRead = cx.prepareStatement("SELECT e.id, " +
					"e.login, " +
					"e.password, " +
					"e.nom, " +
					"e.prenom, " +
					"e.num_ine, " +
					"e.professeur, " +
					"e.classe, " +
					"e.active, " +
					"c.professeur " +
					"FROM etudiant e " +
					"JOIN classe c ON e.classe=c.nom  " +
			"WHERE c.nom = ?");
			psUpdate = cx.prepareStatement("UPDATE classe SET professeur = ? WHERE nom = ? ");
			psDelete = cx.prepareStatement("DELETE FROM classe WHERE nom = ?");
			psFind = cx.prepareStatement("SELECT * FROM classe");
			psEleves = cx.prepareStatement("SELECT * FROM etudiant WHERE classe=?");
			psExist = cx.prepareStatement("SELECT count(*) as exist FROM classe WHERE nom=?");

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public boolean create(Classe c) {
		boolean rs=false;
		try {
			psCreate.setString(1, c.getNom());
			psCreate.setInt(2, c.getProfesseur().getIdCompte());
			psCreate.executeUpdate ();
			rs = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return rs;
	}

	@Override
	public Classe read(String classe) {
		try {
			Classe cl = new Classe();
			cl.setNom(classe);

			ArrayList<Etudiant> eleves = this.eleves(cl);
			cl.setEleves(eleves);

			psRead.setString(1, classe);
			ResultSet rs = psRead.executeQuery();
			if(rs.next()) {
				Professeur professeur = FactoryMySqlDAO.getInstance().createProfDAO().read(rs.getInt("professeur"));
				cl.setProfesseur(professeur);
			}
			return cl;			
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return null;
	}

	@Override
	public boolean update(Classe c) {
		boolean rs=false;
		try {
			psUpdate.setInt(1, c.getProfesseur().getIdCompte());
			psUpdate.setString (2, c.getNom());
			psUpdate.executeUpdate ();
			rs = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return rs;
	}

	@Override
	public boolean delete(Classe c) {
		boolean rs=false;
		try {
			psDelete.setString(1, c.getNom());
			psDelete.executeUpdate ();
			rs = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return rs;
	}

	@Override
	public void addEtudiant(Etudiant e) {

	}

	private ArrayList<Etudiant> eleves(Classe classe) {
		try {
			ArrayList<Etudiant> eleves = new ArrayList<Etudiant>();
			psEleves.setString(1, classe.getNom());
			ResultSet rs = psEleves.executeQuery();
			while(rs.next()) {
				int idCompte = rs.getInt(1);
				String login = rs.getString(2);
				String motDePasse = rs.getString(3);
				String nom = rs.getString(4);
				String prenom = rs.getString(5);
				String numIne = rs.getString(6);			
				ArrayList<Resultat> resultats = FactoryMySqlDAO.getInstance().createEtudDAO().resultats(idCompte);
				Etudiant e = new Etudiant(idCompte, login, motDePasse, nom, prenom, numIne, classe.getProfesseur(), classe, resultats);
				eleves.add(e);
			}
			return eleves;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;	
	}

	@Override
	public ArrayList<Classe> find() {
		try {
			ArrayList<Classe> classes = new ArrayList<Classe>();
			ResultSet rs = psFind.executeQuery();
			while(rs.next()) {
				classes.add(read(rs.getString(1)));
			}
			return classes;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public boolean exist(String nom) {
		try {
			psExist.setString(1, nom);
			ResultSet rs = psExist.executeQuery();
			if(rs.next()) {
				return rs.getInt("exist")==1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return false;
	}

}
